import java.sql.ResultSet;
import java.sql.SQLException;



public class ClubModel {

	/**
	 * Gets a list of clubs
	 * 
	 * @return a 2-D array of clubs. Each club has [0]=id and [1]=name
	 * @throws SQLException if the query failed
	 */
	public static Club[] getClubs(){
		Club clubs[] = null;
		
		ResultSet rs = DB.Query( "SELECT club_id, name, type, rank FROM Club ORDER BY name" );
		
		if( rs != null ){
			try{
				// Get row count to create user array
				rs.last();
				clubs = new Club[rs.getRow()];
				rs.first();
					
				// Store users into array
				for( int i = 0; i < clubs.length; ++i, rs.next() ){
					Club temp = new Club();
					temp.name = rs.getString( "name" );
					temp.id = rs.getInt( "club_id" );
					temp.rank = rs.getString("rank");
					temp.type = rs.getString("type");
					clubs[i] = temp;
				}
			}catch( SQLException e ){
				// SQL Error, return null
				System.err.println( e.getMessage() );
				clubs = null;
			}
		}
		
		return clubs;
	}
	
	
	public static ClubMember[] getClubMembers
		( Club club ) 
	{
		ClubMember[] students = null;
		ResultSet rs = DB.Query("SELECT * FROM Person NATURAL JOIN Member_Of WHERE Member_Of.club_id=" + club.id );
		
		if( rs != null ){
			try{
				// Init student array
				rs.last();
				students = new ClubMember[rs.getRow()];
				rs.first();

				// Fill student array
				for( int i = 0; i < students.length; ++i, rs.next() ){
					ClubMember tmp = new ClubMember();
					tmp.member.first = rs.getString( "first" );
					tmp.member.last = rs.getString( "last" );
					tmp.member.email = rs.getString( "email" );
					tmp.member.uid = Integer.parseInt( rs.getString( "uid" ) );
					tmp.club = club;
					tmp.position = rs.getString( "position" );
					tmp.joined = rs.getString( "join_date" );
					students[i] = tmp;
				}
			}catch( SQLException e ){
				// SQL Error, return null
				System.err.println( e.getMessage() );
				students = null;
			}
		}
		
		return students;
	}
	
	/**
	 * Gets the clubs of a person, slower than by StudentInfo object
	 * @param student UID object of person
	 * @return        Array of clubs or null on an error
	 */
	public static ClubMember[] getClubsOfMember( int uid ){
		return getClubsOfMember( StudentModel.getStudentInfo( uid ) );
	}
	
	/**
	 * Gets the clubs of a person
	 * @param student StudentInfo object of person
	 * @return        Array of clubs or null on an error
	 */
	public static ClubMember[] getClubsOfMember( StudentInfo student ){
		ClubMember[] clubs = null;
		ResultSet rs = DB.Query( "SELECT * FROM Club NATURAL JOIN Member_Of WHERE Member_Of.uid=" + student.uid );
		
		if( rs != null ){
			try{
				// Init club array
				rs.last();
				clubs = new ClubMember[rs.getRow()];
				rs.first();
				
				
				for( int i = 0; i < clubs.length; ++i, rs.next() ){
					ClubMember tmp = new ClubMember();
					tmp.club = new Club();
					tmp.member = student;
					tmp.club.id = rs.getInt("club_id");
					tmp.club.name = rs.getString("name");
					tmp.club.rank = rs.getString("rank");
					tmp.club.type = rs.getString("type");
					tmp.position = rs.getString("position");
					tmp.joined = rs.getString("join_date");
					clubs[i] = tmp;
				}
			}catch( SQLException e ){
				// SQL Error, return null
				System.err.println( e.getMessage() );
				clubs = null;
			}catch( NullPointerException e ){
				// DB.Query failed, return null;
				System.err.println( e.getMessage() );
				clubs = null;
			}
		}
			
		return clubs;
	}
	
	/**
	 * Adds a new club to the system
	 * @param name Name of the club
	 * @param rank Rank of the club
	 * @param type Type of the club
	 * @return     True on success, false on failure
	 */
	public static boolean addClub
		(String name, String rank, String type)
	{
		return DB.SafeUpdate( "INSERT INTO Club (`name`, `rank`, `type`) VALUES (?, ?, ?)", new String[]{name, rank, type} );
	}
	
	/**
	 * Adds a student to a club
	 * @param s Student to add
	 * @param c Club to add to
	 * @return  True on success, false on failure
	 */
	public static boolean addMember
		( StudentInfo s, Club c )
	{
		//Connection cn = DriverManager.getConnection("jdbc:mysql://" + DBHelper.host + "/" + DBHelper.db, DBHelper.username,DBHelper.passwd);
		//Statement sm = cn.createStatement();
//        return DB.Update( "INSERT INTO Member_Of (`uid`, `club_id`, `join_date`) VALUES ('" + s.uid + "', '" + c.id + "', NOW())" );
		return DB.SafeUpdate( "INSERT INTO Member_Of (`uid`, `club_id`, `join_date`) VALUES (?, ?, NOW())", new String[]{""+s.uid, ""+c.id} );

	}
	
	/**
	 * Removes a club from the system
	 * 
	 * @param club_id
	 * @return
	 */
	public static boolean removeClub( int club_id ){
		String query = "DELETE FROM Club WHERE `club_id`=" + club_id;
		return DB.Update( query );
	}
	
	/**
	 * Adds a member to a club with an optional position
	 * @param uid Univ. ID of the student
	 * @param cid Club id
	 * @throws SQLException if a failure occurs
	 */
	public static boolean addMember( StudentInfo s, Club c, String position){
		//ResultSet rs = DB.Query( "SELECT * FROM Member_Of WHERE uid=" + s.uid );
//		return DB.Update("INSERT INTO Member_Of (`uid`, `club_id`, `join_date`, `position`) VALUES ('" + s.uid + "', '" + c.id + "', NOW(), '" + position + "');" );
		return DB.SafeUpdate( "INSERT INTO Member_Of (`uid`, `club_id`, `join_date`, `position`) VALUES (?, ?, NOW(), ?)", new String[]{""+s.uid, ""+c.id, position} );
	}
	
	public static boolean removeMember
		( StudentInfo s, Club c )
		//throws SQLException
	{
		//Connection cn = DriverManager.getConnection("jdbc:mysql://" + DBHelper.host + "/" + DBHelper.db, DBHelper.username,DBHelper.passwd);
		//Statement sm = cn.createStatement();
		return DB.Update( "DELETE FROM Member_Of WHERE `uid`=" + s.uid + " AND `club_id`=" + c.id );
	}
	
	//just a testing method
	//to be removed
	public static void main(String args[])
	{
		try{
			
			// club id 1
			DB.Initialize();
//			ClubMember[] c = getClubsOfMember( 365009842 );
			Club[] cs = getClubs();
			System.out.println( cs[0].id + ":" + cs[0].name );
			
			ClubMember[] c = getClubMembers( cs[0] );
			for( int i = 0; i < c.length; i++ ){
				System.out.println( c[i].club.name + " (" + c[i].position + ")" );
			}
		}
		catch(DBError e)
		{
			System.err.println( e.getMessage() );
		}
	}
	
}
